/*
 * ComparativeClass.java - SQL operations with the table comp_class 
 *  in ksnetcontext (weighted document linking to ontology classes and attributes).
 *
 * Copyright (c) 2006 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbindex;

import java.sql.*;
import java.util.Set;
import java.util.HashSet;

import java.util.*;

public class ComparativeClass {

    /** Creates a new instance of ComparativeClass */
    public ComparativeClass() {
    }

    /** class's id in WebDeso ontology */
    public int      class_id;
    
    /** document's id (autoincremental in the table doc) */
    public int      doc_id;
    
    /** id of ontology that is compared against the document*/
    public int      onto_id;
            
    /** constant in topic based indexing */
    public float    weight;
    
    /** constant in topic based indexing */
    public float    coef_sim;
    
    /** id of the row */
    public int    i_id;
    
    private final static Integer[]          NULL_INTEGER_ARRAY          = new Integer[0];
    private final static ComparativeClass[] NULL_COMPARATIVECLASS_ARRAY = new ComparativeClass[0];
    
    /** Insert element to the comp_class table
     * 
     * SQL example:
     * REPLACE INTO comp_class (class_id,doc_id,weight,coef_sim) VALUES(402,1,0.4,0.4);
     *
     * Test database content by
     * select * from doc,comp_class;
     * select * from doc,comp_class WHERE doc.id=comp_class.doc_id;
     *
     *  @param i_class_id   id of similar class
     *  @param i_doc_id     id of similar document
     */

    public static void Insert (java.sql.Connection conn,int i_class_id,int i_doc_id,int i_onto_id,double f_weight,double f_coef_sim) {
        //int doc_id = 0;
        //int onto_id= 0;
        
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        ComparativeClass compTemp = null;
        
        try 
        /*{
            s = conn.createStatement ();
            
            str_sql = "REPLACE INTO comp_class (class_id,doc_id,weight,coef_sim) VALUES("+
                                                i_class_id + "," +
                                                         i_doc_id + ","+
                                                                f_weight + ","+
                                                                       f_coef_sim + ")";
            s.executeQuery (str_sql);
        }*/
        {
            s = conn.createStatement ();
            compTemp = Get(conn, i_class_id, i_doc_id, i_onto_id);
            if (compTemp == null)
            {
	            str_sql = "INSERT INTO comp_class (class_id, doc_id, onto_id, weight, coef_sim) "+ 
				"VALUES (" + i_class_id + 
				", "+ i_doc_id + 
                ", "+ i_onto_id + 
				", " + f_weight +
				", "+ f_coef_sim + ")";
            	
            } else
            {
	            str_sql = "UPDATE comp_class set " + 
	            				" class_id = " + i_class_id + 
	            				", doc_id ="+ i_doc_id + 
                                ", onto_id ="+ i_onto_id + 
	            				", weight = " + f_weight +
	            				", coef_sim = "+ f_coef_sim + 
	            				" WHERE id = "+ compTemp.i_id;
            }
            s.executeUpdate (str_sql);
        }catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java Insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    /** Selects (class_id,doc_id,weight,coef_sim) from the table comp_class by the documents id and class id.
     *
     *  SQL:
     *  SELECT * FROM comp_class WHERE class_id=3 AND doc_id=1;
     *  
     *  @param class_id     id of class similar to document
     *  @param doc_id       id of documents similar to class
     *  @return ComparativeClass    object with initialized fields: class_id,doc_id,weight,coef_sim
     */
    public static ComparativeClass Get (java.sql.Connection conn,int class_id,int doc_id, int onto_id) {
        ComparativeClass cc = null;
        
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            
            str_sql = "SELECT * FROM comp_class WHERE class_id=" + class_id + " AND doc_id=" + doc_id + " AND onto_id=" + onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                cc = new ComparativeClass();
                cc.class_id = class_id;
                cc.doc_id   = doc_id;
                cc.onto_id = onto_id;
                cc.weight   = rs.getFloat("weight");
                cc.coef_sim = rs.getFloat("coef_sim");
                cc.i_id = rs.getInt("id");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java Get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return cc;
    }

    /** Gets classes id from the table ksnetcontext.comp_class by documents id doc_id.
     * Returns classes with non negative weight, i.e., these are classes
     * created by Direct Indexing method.
     */
    public static int[] getClassIDByDocID(java.sql.Connection conn,int _doc_id,int _onto_id) {
        
        Set<Integer> _class_id = new HashSet<Integer>();
            
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            
            str_sql = "SELECT * FROM comp_class WHERE weight >= 0 AND doc_id=" + _doc_id + " AND onto_id="+_onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            while (rs.next ())
            {
                _class_id.add(rs.getInt("class_id"));
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java getClassIDByDocID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        Integer[] ri = (Integer[])_class_id.toArray(NULL_INTEGER_ARRAY);
        int [] r = new int[ri.length];
        for(int i=0;i<ri.length;i++) {
            r[i] = ri[i];
        }
        return r;
    }
    
    /** Gets classes id from the table ksnetcontext.comp_class by documents id doc_id.
     * Returns classes with negative weight, i.e., these classes are 
     * created by Topic-based Indexing method.
     */
    public static int[] getClassIDWithNegativeWeight(java.sql.Connection conn,int _doc_id,int _onto_id) {
        
        Set<Integer> _class_id = new HashSet<Integer>();
            
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            
            str_sql = "SELECT * FROM comp_class WHERE weight < 0 AND doc_id=" + _doc_id + 
                                                               " AND onto_id="+ _onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            while (rs.next ())
            {   
                _class_id.add(rs.getInt("class_id"));
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java getClassIDByDocID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        Integer[] ri = (Integer[])_class_id.toArray(NULL_INTEGER_ARRAY);
        int [] r = new int[ri.length];
        for(int i=0;i<ri.length;i++) {
            r[i] = ri[i];
        }
        return r;
    }
    
    
    public static ComparativeClass[] getClassWeightByDocID(java.sql.Connection conn,int _doc_id,int _onto_id) {
        
        Set<ComparativeClass> _class_id = new HashSet<ComparativeClass>();
        
        ComparativeClass cc = null;
        
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            
            str_sql = "SELECT * FROM comp_class WHERE doc_id=" + _doc_id + " AND onto_id="+_onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            while (rs.next ())
            {

            	cc = new ComparativeClass();
            	cc.class_id = rs.getInt("class_id");
            	cc.doc_id   = _doc_id;
                cc.onto_id = _onto_id;
                cc.weight   = rs.getFloat("weight");
                cc.coef_sim = rs.getFloat("coef_sim");

                _class_id.add(cc);
                
                cc = null;
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java getClassIDByDocID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        ComparativeClass[] r = (ComparativeClass[])_class_id.toArray(NULL_COMPARATIVECLASS_ARRAY);
        return r;
    }
    
    /** Gets list (wo repetition) of Wikipedia docs id by list of classes id 
     * (it's a wiki list, since only documents with non-empty 'url' will be 
     * selected).
     * <PRE>
     * SQL:
     * new SELECT * FROM ksnetcontext.comp_class,doc 
     *              WHERE   doc_id=doc.id AND 
     *                      length(url)>0 AND 
     *                      class_id IN(1,2) GROUP BY doc_id
     * old SELECT * FROM ksnetcontext.comp_class WHERE class_id IN(1,2) GROUP BY doc_id</PRE>
     */
    public static int[] getDocIDWithUrlByClassID (java.sql.Connection conn,int[] class_id, Language lang) {
        Set<Integer> doc_id = new HashSet<Integer>();
        
        if(null == class_id || 0 == class_id.length) {
            return null;
        }
        String s_in = "" + class_id[0];
        for(int i=1; i<class_id.length; i++) {
            s_in += "," + class_id[i];
        }
            
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            
            str_sql = "SELECT doc_id FROM comp_class,doc WHERE doc_id=doc.id AND length(url)>0 AND class_id IN(" + s_in + 
                    ") AND lang_id="+ lang.toInt() +" GROUP BY doc_id";
            //str_sql = "SELECT doc_id FROM comp_class,doc WHERE doc_id=doc.id AND length(url)>0 AND class_id IN(" + s_in + ") GROUP BY doc_id";
            //str_sql = "SELECT doc_id FROM comp_class WHERE class_id IN(" + s_in + ") GROUP BY doc_id";
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            while (rs.next ())
            {
                doc_id.add(rs.getInt("doc_id"));
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (WebDeso ComparativeClass.java getDocIDWithUrlByClassID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        
        Integer[] ri = (Integer[])doc_id.toArray(NULL_INTEGER_ARRAY);
        int [] r = new int[ri.length];
        for(int i=0;i<ri.length;i++) {
            r[i] = ri[i];
        }
        return r;
    }
}
